package com.hongdee.scm.appAmount.service.impl;

import com.hongdee.common.util.TokenUtil;
import com.hongdee.scm.appAmount.dao.ITScmAmountDao;
import com.hongdee.scm.appAmount.service.ITScmAmountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ThreadFactory;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

/**
 * Created by Administrator on 2017/9/11.
 */
@Service
public class TScmAmountServiceImpl implements ITScmAmountService {

    @Autowired
    private ITScmAmountDao itScmAmountDao;

    /**
     * app全局进销存
     * @param data
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map queryMaAll(Map data, String uid) throws Exception {

        StringBuffer inSql = new StringBuffer();
        StringBuffer saleSql = new StringBuffer();
        StringBuffer stockSql = new StringBuffer();
        StringBuffer onSql = new StringBuffer();
        StringBuffer overSql = new StringBuffer();
        List inParam = new ArrayList();
        List saleParam = new ArrayList();
        List stockParam = new ArrayList();
        List onParam = new ArrayList();
        List overParam = new ArrayList();

        //登录人下的所有公司
        List<Map<String,Object>> companyMaps= TokenUtil.getDataCompany(uid);

       //进
       inSql.append("select ifnull(count(1),0) as inAmount," +
               "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as inPrice" +
               " from t_scm_enter_stock_record r inner join t_scm_car c on r.car_id = c.id" +
               " where r.enter_type='1' and c.company_id in ( " );
        for(int i=0;i<companyMaps.size();i++){
            inSql.append("?");
            Map infoMap=companyMaps.get(i);
            String id=String.valueOf(infoMap.get("id"));
            inParam.add(id);
            if(i<(companyMaps.size()-1)){
                inSql.append(" , ");
            }
        }
        inSql.append(" ) ");
       //销
       saleSql.append("select ifnull(sum(t.invoice_price),0) as salePrice,ifnull(sum(t.car_count),0) as saleAmount " +
               " from ( select s.invoice_price,s.car_count from t_scm_sale_order s" +
               " where s.status='0' and s.company_id in (");
        for(int i=0;i<companyMaps.size();i++){
            saleSql.append("?");
            Map infoMap=companyMaps.get(i);
            String id=String.valueOf(infoMap.get("id"));
            saleParam.add(id);
            if(i<(companyMaps.size()-1)){
                saleSql.append(" , ");
            }
        }
        saleSql.append(" ) ");

        //开始日期
        if(data.get("startDay")!=null&&!data.get("startDay").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m-%d')>=?");
            inParam.add(data.get("startDay"));

            saleSql.append(" and s.invoice_date>=?");
            saleParam.add(data.get("startDay"));

        }
        //结束日期
        if(data.get("stopDay")!=null&&!data.get("stopDay").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m-%d')<=?");
            inParam.add(data.get("stopDay"));

            saleSql.append(" and s.invoice_date<=?");
            saleParam.add(data.get("stopDay"));

            //存
            stockSql.append("select ifnull(count(1),0) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as stockPrice" +
                    " from t_scm_car c where EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where " +
                    " DATE_FORMAT(r.enter_date,'%Y-%m-%d')<=? and c.id = r.car_id ) and not EXISTS (" +
                    " select o.car_id from t_scm_outwarehouse o where DATE_FORMAT(o.out_date,'%Y-%m-%d')<=?" +
                    " and o.out_status ='1' and o.car_id = c.id)" +
                    " and c.company_id in( ");
            stockParam.add(data.get("stopDay"));
            stockParam.add(data.get("stopDay"));
            for(int i=0;i<companyMaps.size();i++){
                stockSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                stockParam.add(id);
                if(i<(companyMaps.size()-1)){
                    stockSql.append(" , ");
                }
            }
            stockSql.append(" ) ");

            //在途
            onSql.append("select ifnull(count(1),0) as wayAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as wayPrice" +
                    " from t_scm_car c where not EXISTS ( select * from t_scm_enter_stock_record r " +
                    " where r.car_id = c.id and DATE_FORMAT(r.enter_date,'%Y-%m-%d')<=? " +
                    ") and FROM_UNIXTIME(c.create_time/1000,'%Y-%m-%d') <=? and c.company_id in ( ");
            onParam.add(data.get("stopDay"));
            onParam.add(data.get("stopDay"));
            for(int i=0;i<companyMaps.size();i++){
                onSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                onParam.add(id);
                if(i<(companyMaps.size()-1)){
                    onSql.append(" , ");
                }
            }
            onSql.append(" ) ");

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount, " +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id" +
                    " where TIMESTAMPDIFF(DAY,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180"+
                    " and c.company_id in ( ");
            overParam.add(data.get("stopDay"));
            for(int i=0;i<companyMaps.size();i++){
                overSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                overParam.add(id);
                if(i<(companyMaps.size()-1)){
                    overSql.append(" , ");
                }
            }
            overSql.append(" ) ");
        }

        //开始月
        if(data.get("startMonth")!=null&&!data.get("startMonth").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')>=?");
            inParam.add(data.get("startMonth"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')>=?");
            saleParam.add(data.get("startMonth"));

        }
        //结束月
        if(data.get("stopMonth")!=null&&!data.get("stopMonth").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')<=?");
            inParam.add(data.get("stopMonth"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')<=?");
            saleParam.add(data.get("stopMonth"));

            //存
            stockSql.append("select ifnull(count(1),0) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as stockPrice" +
                    " from t_scm_car c where EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where " +
                    " DATE_FORMAT(r.enter_date,'%Y-%m')<=? and c.id = r.car_id ) and not EXISTS (" +
                    " select o.car_id from t_scm_outwarehouse o where DATE_FORMAT(o.out_date,'%Y-%m')<=?" +
                    " and o.out_status ='1' and o.car_id = c.id )" +
                    " and c.company_id in( ");
            stockParam.add(data.get("stopMonth"));
            stockParam.add(data.get("stopMonth"));
            for(int i=0;i<companyMaps.size();i++){
                stockSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                stockParam.add(id);
                if(i<(companyMaps.size()-1)){
                    stockSql.append(" , ");
                }
            }
            stockSql.append(" ) ");

            //在途
            onSql.append("select ifnull(count(1),0) as wayAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as wayPrice" +
                    " from t_scm_car c where not EXISTS ( select * from t_scm_enter_stock_record r " +
                    " where r.car_id = c.id and DATE_FORMAT(r.enter_date,'%Y-%m')<=? " +
                    ") and FROM_UNIXTIME(c.create_time/1000,'%Y-%m') <=? and c.company_id in (");
            onParam.add(data.get("stopMonth"));
            onParam.add(data.get("stopMonth"));
            for(int i=0;i<companyMaps.size();i++){
                onSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                onParam.add(id);
                if(i<(companyMaps.size()-1)){
                    onSql.append(" , ");
                }
            }
            onSql.append(" ) ");

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount, " +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id" +
                    " where TIMESTAMPDIFF(DAY,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180"+
                    " and c.company_id in ( ");
            String stopDay = this.getMonthLastDay(data.get("stopMonth").toString());
            overParam.add(stopDay);
            for(int i=0;i<companyMaps.size();i++){
                overSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                overParam.add(id);
                if(i<(companyMaps.size()-1)){
                    overSql.append(" , ");
                }
            }
            overSql.append(" ) ");
        }

        //开始季度
        if(data.get("startSeason")!=null&&!data.get("startSeason").equals("")){

            String month = "";
            if(data.get("startSeason").toString().length()==5){
                month = this.findMonth(data.get("startSeason").toString(),"1");
            }else{
                month = data.get("startSeason").toString();
            }
            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')>=?");
            inParam.add(month);

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')>=?");
            saleParam.add(month);
        }
        //结束季度
        if(data.get("stopSeason")!=null&&!data.get("stopSeason").equals("")){

            String month = "";
            if(data.get("stopSeason").toString().length()==5){
                month = this.findMonth(data.get("stopSeason").toString(),"2");
            }else{
                month = data.get("stopSeason").toString();
            }

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')<=?");
            inParam.add(month);

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')<=?");
            saleParam.add(month);

            //存
            stockSql.append("select ifnull(count(1),0) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as stockPrice" +
                    " from t_scm_car c where EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where" +
                    " DATE_FORMAT(r.enter_date,'%Y-%m')<=? and c.id = r.car_id ) and not EXISTS (" +
                    " select o.car_id from t_scm_outwarehouse o where DATE_FORMAT(o.out_date,'%Y-%m')<=?" +
                    " and o.out_status ='1' and o.car_id = c.id ) and c.company_id in ( ");
            stockParam.add(month);
            stockParam.add(month);
            for(int i=0;i<companyMaps.size();i++){
                stockSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                stockParam.add(id);
                if(i<(companyMaps.size()-1)){
                    stockSql.append(" , ");
                }
            }
            stockSql.append(" ) ");

            //在途
            onSql.append("select ifnull(count(1),0) as wayAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as wayPrice" +
                    " from t_scm_car c where not EXISTS ( select * from t_scm_enter_stock_record r " +
                    " where r.car_id = c.id and DATE_FORMAT(r.enter_date,'%Y-%m')<=? " +
                    ") and FROM_UNIXTIME(c.create_time/1000,'%Y-%m') <=? and c.company_id in(");
            onParam.add(month);
            onParam.add(month);
            for(int i=0;i<companyMaps.size();i++){
                onSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                onParam.add(id);
                if(i<(companyMaps.size()-1)){
                    onSql.append(" , ");
                }
            }
            onSql.append(" ) ");

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount, " +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id" +
                    " where TIMESTAMPDIFF(DAY,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180"+
                    " and c.company_id in ( ");
            String stopDay = this.getMonthLastDay(month);
            overParam.add(stopDay);
            for(int i=0;i<companyMaps.size();i++){
                overSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                overParam.add(id);
                if(i<(companyMaps.size()-1)){
                    overSql.append(" , ");
                }
            }
            overSql.append(" ) ");

        }

        //开始年
        if(data.get("startYear")!=null&&!data.get("startYear").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y')>=?");
            inParam.add(data.get("startYear"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y')>=?");
            saleParam.add(data.get("startYear"));
        }
        //结束年
        if(data.get("stopYear")!=null&&!data.get("stopYear").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y')<=?");
            inParam.add(data.get("stopYear"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y')<=?");
            saleParam.add(data.get("stopYear"));

            //存
            stockSql.append("select ifnull(count(1),0) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as stockPrice" +
                    " from t_scm_car c where EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where" +
                    " DATE_FORMAT(r.enter_date,'%Y')<=? and c.id = r.car_id ) and not EXISTS (" +
                    " select o.car_id from t_scm_outwarehouse o where DATE_FORMAT(o.out_date,'%Y')<=?" +
                    " and o.out_status ='1' and o.car_id = c.id) and  c.company_id in ( ");
            stockParam.add(data.get("stopYear"));
            stockParam.add(data.get("stopYear"));
            for(int i=0;i<companyMaps.size();i++){
                stockSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                stockParam.add(id);
                if(i<(companyMaps.size()-1)){
                    stockSql.append(" , ");
                }
            }
            stockSql.append(" ) ");

            //在途
            onSql.append("select ifnull(count(1),0) as wayAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as wayPrice" +
                    " from t_scm_car c where not EXISTS ( select * from t_scm_enter_stock_record r " +
                    " where r.car_id = c.id and DATE_FORMAT(r.enter_date,'%Y')<=?" +
                    ") and FROM_UNIXTIME(c.create_time/1000,'%Y') <=? and c.company_id in ( ");
            onParam.add(data.get("stopYear"));
            onParam.add(data.get("stopYear"));
            for(int i=0;i<companyMaps.size();i++){
                onSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                onParam.add(id);
                if(i<(companyMaps.size()-1)){
                    onSql.append(" , ");
                }
            }
            onSql.append(" ) ");

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount, " +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id" +
                    " where TIMESTAMPDIFF(DAY,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180"+
                    " and c.company_id in ( ");
            String stopDay = data.get("stopYear").toString()+"-12-31";
            overParam.add(stopDay);
            for(int i=0;i<companyMaps.size();i++){
                overSql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                overParam.add(id);
                if(i<(companyMaps.size()-1)){
                    overSql.append(" , ");
                }
            }
            overSql.append(" ) ");
        }

        saleSql.append(" ) t");

        Map maps = new HashMap();
        Map amount = new HashMap();
        Map price = new HashMap();
        Map inMap = this.itScmAmountDao.info(inSql.toString(),inParam.toArray());
        amount.put("in",inMap.get("inAmount"));
        price.put("in",inMap.get("inPrice"));
        Map saleMap = this.itScmAmountDao.info(saleSql.toString(),saleParam.toArray());
        amount.put("sale",saleMap.get("saleAmount"));
        price.put("sale",saleMap.get("salePrice"));
        Map stockMap = this.itScmAmountDao.info(stockSql.toString(),stockParam.toArray());
        amount.put("stock",stockMap.get("stockAmount"));
        price.put("stock",stockMap.get("stockPrice"));
        Map onMap = this.itScmAmountDao.info(onSql.toString(),onParam.toArray());
        amount.put("way",onMap.get("wayAmount"));
        price.put("way",onMap.get("wayPrice"));
        Map overMap = this.itScmAmountDao.info(overSql.toString(),overParam.toArray());
        amount.put("over",overMap.get("overAmount"));
        price.put("over",overMap.get("overPrice"));

        maps.put("amount",amount);
        maps.put("price",price);
        return maps;
    }

    /**
     * 全局进销存返回方法
     * @param data
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public List queryQuanju(Map data,String uid) throws Exception{

        Map maps = new HashMap();
        //原数据
        Map origin = this.queryMaAll(data,uid);
        maps.put("origin",origin);
        //同比数据
        Map timeT = this.calculateTime(data,"1");
        Map mapTong = this.queryMaAll(timeT,uid);
        maps.put("tongbi",mapTong);
        //环比数据
        Map timeH = this.calculateTime(data,"2");
        Map mapHuan = this.queryMaAll(timeH,uid);
        maps.put("huanbi",mapHuan);

        List dataList = new ArrayList();
        Map mm = new HashMap();
        mm.put("singleWholeInvoicingData",maps);
        dataList.add(mm);
        return dataList;
    }

    /**
     * 品牌进销存
     * @param data
     * @param uid
     * @return
     */
    @Override
    public Map queryMaBrand(Map data,String uid) throws Exception{

        StringBuffer inSql = new StringBuffer();
        StringBuffer saleSql = new StringBuffer();
        StringBuffer stockSql = new StringBuffer();
        StringBuffer onSql = new StringBuffer();
        StringBuffer overSql = new StringBuffer();
        List inParam = new ArrayList();
        List saleParam = new ArrayList();
        List stockParam = new ArrayList();
        List onParam = new ArrayList();
        List overParam = new ArrayList();

        String cid = TokenUtil.getCompanyId(uid);
        //登录人下的所有公司
        List<Map<String,Object>> companyMaps= TokenUtil.getDataCompany(uid);
        //进
        inSql.append("select ifnull(count(1),0) as inAmount," +
                "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as inPrice" +
                " from t_scm_car c inner join t_scm_enter_stock_record r on c.id = r.car_id" +
                " where r.enter_type='1' and  c.company_id in ( select id from t_bc_organ where id in (" +
                " select org_id from t_bc_role_data_org where role_id in ( select r_id from t_bc_role_user" +
                " where u_id =?)) and o_type ='1' and o_p_id = ? union ALL select ?)");
        inParam.add(uid);
        inParam.add(data.get("brand"));
        inParam.add(cid);
        //销
        saleSql.append("select ifnull(sum(t.invoice_price),0) as salePrice,ifnull(sum(t.car_count),0) as saleAmount" +
                " from ( select s.invoice_price,s.car_count " +
                " from t_scm_sale_order s inner join t_scm_sale_order_detail d on s.id = d.sale_order_id" +
                " inner join t_scm_car c on d.car_id = c.id where s.status='0' and c.company_id in" +
                " ( select id from t_bc_organ where id in ( select org_id from t_bc_role_data_org" +
                " where role_id in (select r_id from t_bc_role_user where u_id = ? )) and o_type ='1'" +
                " and o_p_id=? union ALL select ?)");
        saleParam.add(uid);
        saleParam.add(data.get("brand"));
        saleParam.add(cid);

        //开始日期
        if(data.get("startDay")!=null&&!data.get("startDay").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m-%d')>=?");
            inParam.add(data.get("startDay"));

            saleSql.append(" and s.invoice_date>=?");
            saleParam.add(data.get("startDay"));
        }
        //结束日期
        if(data.get("stopDay")!=null&&!data.get("stopDay").equals("")){
            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m-%d')<=?");
            inParam.add(data.get("stopDay"));

            saleSql.append(" and s.invoice_date<=?");
            saleParam.add(data.get("stopDay"));

            stockSql.append("select count(1) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0)  as stockPrice" +
                    " from t_scm_car c where c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ? ) and EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where DATE_FORMAT(r.enter_date,'%Y-%m-%d')<=?" +
                    " and c.id = r.car_id ) and not EXISTS ( select o.car_id from t_scm_outwarehouse o" +
                    " where DATE_FORMAT(o.out_date,'%Y-%m-%d')<=? and o.out_status ='1' and o.car_id = c.id)");
            stockParam.add(uid);
            stockParam.add(data.get("brand"));
            stockParam.add(cid);
            stockParam.add(data.get("stopDay"));
            stockParam.add(data.get("stopDay"));

            onSql.append("select count(1) as onAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as onPrice" +
                    " from t_scm_car c where c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ?"+
                    ") and not EXISTS ( select * from t_scm_enter_stock_record r where r.car_id = c.id" +
                    " and DATE_FORMAT(r.enter_date,'%Y-%m-%d')<=? )" +
                    " and FROM_UNIXTIME(c.create_time/1000,'%Y-%m-%d') <= ?");
            onParam.add(uid);
            onParam.add(data.get("brand"));
            onParam.add(cid);
            onParam.add(data.get("stopDay"));
            onParam.add(data.get("stopDay"));

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount, " +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id" +
                    " where TIMESTAMPDIFF(DAY,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180"+
                    " and c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ? )");
            overParam.add(data.get("stopDay"));
            overParam.add(uid);
            overParam.add(data.get("brand"));
            overParam.add(cid);
        }

        //开始月
        if(data.get("startMonth")!=null&&!data.get("startMonth").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')>=?");
            inParam.add(data.get("startMonth"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')>=?");
            saleParam.add(data.get("startMonth"));

        }
        //结束月
        if(data.get("stopMonth")!=null&&!data.get("stopMonth").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')<=?");
            inParam.add(data.get("stopMonth"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')<=?");
            saleParam.add(data.get("stopMonth"));

            stockSql.append("select count(1) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0)  as stockPrice" +
                    " from t_scm_car c where c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ? ) and EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where DATE_FORMAT(r.enter_date,'%Y-%m')<=?" +
                    " and c.id = r.car_id ) and not EXISTS ( select o.car_id from t_scm_outwarehouse o" +
                    " where DATE_FORMAT(o.out_date,'%Y-%m')<=? and o.out_status ='1' and o.car_id = c.id)");
            stockParam.add(uid);
            stockParam.add(data.get("brand"));
            stockParam.add(cid);
            stockParam.add(data.get("stopMonth"));
            stockParam.add(data.get("stopMonth"));

            onSql.append("select count(1) as onAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as onPrice" +
                    " from t_scm_car c where c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ?"+
                    ") and not EXISTS ( select * from t_scm_enter_stock_record r where r.car_id = c.id" +
                    " and DATE_FORMAT(r.enter_date,'%Y-%m')<=? )" +
                    " and FROM_UNIXTIME(c.create_time/1000,'%Y-%m') <= ?");
            onParam.add(uid);
            onParam.add(data.get("brand"));
            onParam.add(cid);
            onParam.add(data.get("stopMonth"));
            onParam.add(data.get("stopMonth"));

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount, " +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id" +
                    " where TIMESTAMPDIFF(DAY,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180"+
                    " and c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ? )");
            String stopDay = this.getMonthLastDay(data.get("stopMonth").toString());
            overParam.add(stopDay);
            overParam.add(uid);
            overParam.add(data.get("brand"));
            overParam.add(cid);

        }

        //开始季度
        if(data.get("startSeason")!=null&&!data.get("startSeason").equals("")){

            String month = "";
            if(data.get("startSeason").toString().length()==5){
                month = this.findMonth(data.get("startSeason").toString(),"1");
            }else{
                month = data.get("startSeason").toString();
            }

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')>=?");
            inParam.add(month);

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')>=?");
            saleParam.add(month);
        }
        //结束季度
        if(data.get("stopSeason")!=null&&!data.get("stopSeason").equals("")){

            String month = "";
            if(data.get("stopSeason").toString().length()==5){
                month = this.findMonth(data.get("stopSeason").toString(),"2");
            }else{
                month = data.get("stopSeason").toString();
            }

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')<=?");
            inParam.add(month);

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')<=?");
            saleParam.add(month);

            stockSql.append("select count(1) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0)  as stockPrice" +
                    " from t_scm_car c where c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ? ) and EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where DATE_FORMAT(r.enter_date,'%Y-%m')<=?" +
                    " and c.id = r.car_id ) and not EXISTS ( select o.car_id from t_scm_outwarehouse o" +
                    " where DATE_FORMAT(o.out_date,'%Y-%m')<=? and o.out_status ='1' and o.car_id = c.id)");
            stockParam.add(uid);
            stockParam.add(data.get("brand"));
            stockParam.add(cid);
            stockParam.add(month);
            stockParam.add(month);

            onSql.append("select count(1) as onAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as onPrice" +
                    " from t_scm_car c where c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ?"+
                    ") and not EXISTS ( select * from t_scm_enter_stock_record r where r.car_id = c.id" +
                    " and DATE_FORMAT(r.enter_date,'%Y-%m')<=? )" +
                    " and FROM_UNIXTIME(c.create_time/1000,'%Y-%m') <= ?");
            onParam.add(uid);
            onParam.add(data.get("brand"));
            onParam.add(cid);
            onParam.add(month);
            onParam.add(month);

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount, " +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id" +
                    " where TIMESTAMPDIFF(DAY,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180"+
                    " and c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ? )");
            String stopDay = this.getMonthLastDay(month);
            overParam.add(stopDay);
            overParam.add(uid);
            overParam.add(data.get("brand"));
            overParam.add(cid);

        }

        //开始年
        if(data.get("startYear")!=null&&!data.get("startYear").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y')>=?");
            inParam.add(data.get("startYear"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y')>=?");
            saleParam.add(data.get("startYear"));
        }
        //结束年
        if(data.get("stopYear")!=null&&!data.get("stopYear").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y')<=?");
            inParam.add(data.get("stopYear"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y')<=?");
            saleParam.add(data.get("stopYear"));

            stockSql.append("select count(1) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0)  as stockPrice" +
                    " from t_scm_car c where c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ? ) and EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where DATE_FORMAT(r.enter_date,'%Y')<=?" +
                    " and c.id = r.car_id ) and not EXISTS ( select o.car_id from t_scm_outwarehouse o" +
                    " where DATE_FORMAT(o.out_date,'%Y')<=? and o.out_status ='1' and o.car_id = c.id)");
            stockParam.add(uid);
            stockParam.add(data.get("brand"));
            stockParam.add(cid);
            stockParam.add(data.get("stopYear"));
            stockParam.add(data.get("stopYear"));

            onSql.append("select count(1) as onAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as onPrice" +
                    " from t_scm_car c where c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ?"+
                    ") and not EXISTS ( select * from t_scm_enter_stock_record r where r.car_id = c.id" +
                    " and DATE_FORMAT(r.enter_date,'%Y')<=? )" +
                    " and FROM_UNIXTIME(c.create_time/1000,'%Y') <= ?");
            onParam.add(uid);
            onParam.add(data.get("brand"));
            onParam.add(cid);
            onParam.add(data.get("stopYear"));
            onParam.add(data.get("stopYear"));

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount, " +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id" +
                    " where TIMESTAMPDIFF(DAY,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180"+
                    " and c.company_id in ( select id from t_bc_organ where id in (" +
                    " select org_id from t_bc_role_data_org where role_id in (select r_id from t_bc_role_user" +
                    " where u_id = ?)) and o_type ='1' and o_p_id=? union ALL select ? )");
            String stopDay = data.get("stopYear").toString()+"-21-31";
            overParam.add(stopDay);
            overParam.add(uid);
            overParam.add(data.get("brand"));
            overParam.add(cid);

        }

        saleSql.append(" ) t");

        Map maps = new HashMap();
        Map amount = new HashMap();
        Map price = new HashMap();
        Map inMap = this.itScmAmountDao.info(inSql.toString(),inParam.toArray());
        amount.put("in",inMap.get("inAmount"));
        price.put("in",inMap.get("inPrice"));
        Map saleMap = this.itScmAmountDao.info(saleSql.toString(),saleParam.toArray());
        amount.put("sale",saleMap.get("saleAmount"));
        price.put("sale",saleMap.get("salePrice"));
        Map stockMap = this.itScmAmountDao.info(stockSql.toString(),stockParam.toArray());
        amount.put("stock",stockMap.get("stockAmount"));
        price.put("stock",stockMap.get("stockPrice"));
        Map onMap = this.itScmAmountDao.info(onSql.toString(),onParam.toArray());
        amount.put("way",onMap.get("onAmount"));
        price.put("way",onMap.get("onPrice"));
        Map overMap = this.itScmAmountDao.info(overSql.toString(),overParam.toArray());
        amount.put("over",overMap.get("overAmount"));
        price.put("over",overMap.get("overPrice"));

        maps.put("amount",amount);
        maps.put("price",price);
        return maps;
    }

    /**
     * 品牌返回数据的方法
     * @param data
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public List queryBrand(Map data,String uid) throws Exception{

        List dataList = new ArrayList();
        List<Map<String,Object>> brand = (ArrayList) data.get("brands");
        List<Map<String,Object>> brandList = new ArrayList();
        for(Map<String,Object> b:brand){
            Map mm = new HashMap();
            Map brandId = this.itScmAmountDao.info("select * from t_bc_organ where o_short_name=?",b.get("bName"));
            mm.put("id",brandId.get("id"));
            mm.put("name",b.get("bName"));
            brandList.add(mm);
        }
        for(Map<String,Object> b:brandList){

            Map mm = new HashMap();
            Map maps = new HashMap();

            mm.put("id",b.get("id"));
            mm.put("name",b.get("name"));

            //原数据
            data.put("brand",b.get("id"));
            Map origin = this.queryMaBrand(data,uid);
            maps.put("origin",origin);
            //同比数据
            Map timeT = this.calculateTime(data,"1");
            timeT.put("brand",b.get("id"));
            Map mapTong = this.queryMaBrand(timeT,uid);
            maps.put("tongbi",mapTong);
            //环比数据
            Map timeH = this.calculateTime(data,"2");
            timeH.put("brand",b.get("id"));
            Map mapHuan = this.queryMaBrand(timeH,uid);
            maps.put("huanbi",mapHuan);

            mm.put("singleWholeInvoicingData",maps);
            dataList.add(mm);
        }
        return dataList;
    }

    /**
     * 公司进销存返回方法
     * @param data
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public List queryCompany(Map data,String uid) throws Exception{

        List dataList = new ArrayList();
        List<Map<String,Object>> companys = (ArrayList) data.get("companys");

        for(Map<String,Object> com:companys){

            Map mm = new HashMap();
            Map maps = new HashMap();

            //获取该公司的品牌名称和代码
            Map brand = this.itScmAmountDao.info("select id,o_short_name from t_bc_organ where id in (" +
                    " select o_p_id from t_bc_organ where id = ? and status = '1')",com.get("id"));
            mm.put("id",com.get("id"));
            mm.put("name",com.get("name"));
            mm.put("parentId",brand.get("id"));
            mm.put("parentName",brand.get("oShortName"));

            //原数据
            data.put("companyId",com.get("id"));
            Map origin = this.queryMaCompany(data,uid);
            maps.put("origin",origin);
            //同比数据
            Map timeT = this.calculateTime(data,"1");
            timeT.put("companyId",com.get("id"));
            Map mapTong = this.queryMaCompany(timeT,uid);
            maps.put("tongbi",mapTong);
            //环比数据
            Map timeH = this.calculateTime(data,"2");
            timeH.put("companyId",com.get("id"));
            Map mapHuan = this.queryMaCompany(timeH,uid);
            maps.put("huanbi",mapHuan);

            mm.put("singleWholeInvoicingData",maps);
            dataList.add(mm);
        }
        return dataList;
    }

    /**
     * 公司进销存
     * @param data
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map queryMaCompany(Map data,String uid) throws Exception{

        StringBuffer inSql = new StringBuffer();
        StringBuffer saleSql = new StringBuffer();
        StringBuffer stockSql = new StringBuffer();
        StringBuffer onSql = new StringBuffer();
        StringBuffer overSql = new StringBuffer();
        List inParam = new ArrayList();
        List saleParam = new ArrayList();
        List stockParam = new ArrayList();
        List onParam = new ArrayList();
        List overParam = new ArrayList();

        String cid = data.get("companyId").toString();

        //进
        inSql.append("select ifnull(count(1),0) as inAmount," +
                "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as inPrice" +
                " from t_scm_enter_stock_record r inner join t_scm_car c on r.car_id = c.id" +
                " where r.enter_type='1' and r.company_id=?" );
        inParam.add(cid);
        //销
        saleSql.append("select ifnull(sum(t.invoice_price),0) as salePrice,ifnull(sum(t.car_count),0) as saleAmount " +
                " from ( select s.invoice_price,s.car_count from t_scm_sale_order s" +
                " where s.status='0' and s.company_id=?");
        saleParam.add(cid);

        //开始日期
        if(data.get("startDay")!=null&&!data.get("startDay").equals("")){

            inSql.append(" and r.enter_date>=?");
            inParam.add(data.get("startDay"));

            saleSql.append(" and s.invoice_date>=?");
            saleParam.add(data.get("startDay"));
        }
        //结束日期
        if(data.get("stopDay")!=null&&!data.get("stopDay").equals("")){

            inSql.append(" and r.enter_date<=?");
            inParam.add(data.get("stopDay"));

            saleSql.append(" and s.invoice_date<=?");
            saleParam.add(data.get("stopDay"));

            //存
            stockSql.append("select ifnull(count(1),0) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as stockPrice" +
                    " from t_scm_car c where c.company_id=? and EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where r.company_id=?" +
                    " and r.enter_date<=? and c.id = r.car_id ) and not EXISTS (" +
                    " select o.car_id from t_scm_outwarehouse o where o.out_date<=?" +
                    " and o.company_id=? and o.out_status ='1' and o.car_id = c.id)");

            stockParam.add(cid);
            stockParam.add(cid);
            stockParam.add(data.get("stopDay"));
            stockParam.add(data.get("stopDay"));
            stockParam.add(cid);

            //在途
            onSql.append("select ifnull(count(1),0) as wayAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as wayPrice" +
                    " from t_scm_car c where not EXISTS ( select * from t_scm_enter_stock_record r " +
                    " where r.car_id = c.id and r.company_id=? and r.enter_date<=? " +
                    ")and c.company_id=? and FROM_UNIXTIME(c.create_time/1000,'%Y-%m-%d') <=?");

            onParam.add(cid);
            onParam.add(data.get("stopDay"));
            onParam.add(cid);
            onParam.add(data.get("stopDay"));

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id " +
                    " where TIMESTAMPDIFF(day,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180 " +
                    " and c.company_id=?");
            overParam.add(data.get("stopDay"));
            overParam.add(cid);
        }

        //开始月
        if(data.get("startMonth")!=null&&!data.get("startMonth").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')>=?");
            inParam.add(data.get("startMonth"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')>=?");
            saleParam.add(data.get("startMonth"));

        }
        //结束月
        if(data.get("stopMonth")!=null&&!data.get("stopMonth").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')<=?");
            inParam.add(data.get("stopMonth"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')<=?");
            saleParam.add(data.get("stopMonth"));

            //存
            stockSql.append("select ifnull(count(1),0) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as stockPrice" +
                    " from t_scm_car c where c.company_id=? and EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where r.company_id=?" +
                    " and DATE_FORMAT(r.enter_date,'%Y-%m')<=? and c.id = r.car_id ) and not EXISTS (" +
                    " select o.car_id from t_scm_outwarehouse o where DATE_FORMAT(o.out_date,'%Y-%m')<=?" +
                    " and o.company_id=? and o.out_status ='1' and o.car_id = c.id)");

            stockParam.add(cid);
            stockParam.add(cid);
            stockParam.add(data.get("stopMonth"));
            stockParam.add(data.get("stopMonth"));
            stockParam.add(cid);

            //在途
            onSql.append("select ifnull(count(1),0) as wayAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as wayPrice" +
                    " from t_scm_car c where not EXISTS ( select * from t_scm_enter_stock_record r " +
                    " where r.car_id = c.id and r.company_id=? and DATE_FORMAT(r.enter_date,'%Y-%m')<=? " +
                    ")and c.company_id=? and FROM_UNIXTIME(c.create_time/1000,'%Y-%m') <=?");

            onParam.add(cid);
            onParam.add(data.get("stopMonth"));
            onParam.add(cid);
            onParam.add(data.get("stopMonth"));

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id " +
                    " where TIMESTAMPDIFF(day,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180 " +
                    " and c.company_id=?");
            String stopDay = this.getMonthLastDay(data.get("stopMonth").toString());
            overParam.add(stopDay);
            overParam.add(cid);
        }

        //开始季度
        if(data.get("startSeason")!=null&&!data.get("startSeason").equals("")){

            String month = "";
            if(data.get("startSeason").toString().length()==5){
                month = this.findMonth(data.get("startSeason").toString(),"1");
            }else{
                month = data.get("startSeason").toString();
            }

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')>=?");
            inParam.add(month);

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')>=?");
            saleParam.add(month);
        }
        //结束季度
        if(data.get("stopSeason")!=null&&!data.get("stopSeason").equals("")){

            String month = "";
            if(data.get("stopSeason").toString().length()==5){
                month = this.findMonth(data.get("stopSeason").toString(),"2");
            }else{
                month = data.get("stopSeason").toString();
            }

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y-%m')<=?");
            inParam.add(month);

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y-%m')<=?");
            saleParam.add(month);

            //存
            stockSql.append("select ifnull(count(1),0) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as stockPrice" +
                    " from t_scm_car c where c.company_id=? and EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where r.company_id=?" +
                    " and DATE_FORMAT(r.enter_date,'%Y-%m')<=? and c.id = r.car_id ) and not EXISTS (" +
                    " select o.car_id from t_scm_outwarehouse o where DATE_FORMAT(o.out_date,'%Y-%m')<=?" +
                    " and o.company_id=? and o.out_status ='1' and o.car_id = c.id)");

            stockParam.add(cid);
            stockParam.add(cid);
            stockParam.add(month);
            stockParam.add(month);
            stockParam.add(cid);

            //在途
            onSql.append("select ifnull(count(1),0) as wayAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as wayPrice" +
                    " from t_scm_car c where not EXISTS ( select * from t_scm_enter_stock_record r " +
                    " where r.car_id = c.id and r.company_id=? and DATE_FORMAT(r.enter_date,'%Y-%m')<=? " +
                    ")and c.company_id=? and FROM_UNIXTIME(c.create_time/1000,'%Y-%m') <=?");

            onParam.add(cid);
            onParam.add(month);
            onParam.add(cid);
            onParam.add(month);

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id " +
                    " where TIMESTAMPDIFF(day,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180 " +
                    " and c.company_id=?");
            String stopDay = this.getMonthLastDay(month);
            overParam.add(stopDay);
            overParam.add(cid);

        }

        //开始年
        if(data.get("startYear")!=null&&!data.get("startYear").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y')>=?");
            inParam.add(data.get("startYear"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y')>=?");
            saleParam.add(data.get("startYear"));
        }
        //结束年
        if(data.get("stopYear")!=null&&!data.get("stopYear").equals("")){

            inSql.append(" and DATE_FORMAT(r.enter_date,'%Y')<=?");
            inParam.add(data.get("stopYear"));

            saleSql.append(" and DATE_FORMAT(s.invoice_date,'%Y')<=?");
            saleParam.add(data.get("stopYear"));

            //存
            stockSql.append("select ifnull(count(1),0) as stockAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as stockPrice" +
                    " from t_scm_car c where c.company_id=? and EXISTS(" +
                    " select r.car_id from t_scm_enter_stock_record r where r.company_id=?" +
                    " and DATE_FORMAT(r.enter_date,'%Y')<=? and c.id = r.car_id ) and not EXISTS (" +
                    " select o.car_id from t_scm_outwarehouse o where DATE_FORMAT(o.out_date,'%Y')<=?" +
                    " and o.company_id=? and o.out_status ='1' and o.car_id = c.id)");

            stockParam.add(cid);
            stockParam.add(cid);
            stockParam.add(data.get("stopYear"));
            stockParam.add(data.get("stopYear"));
            stockParam.add(cid);

            //在途
            onSql.append("select ifnull(count(1),0) as wayAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as wayPrice" +
                    " from t_scm_car c where not EXISTS ( select * from t_scm_enter_stock_record r " +
                    " where r.car_id = c.id and r.company_id=? and DATE_FORMAT(r.enter_date,'%Y')<=? " +
                    ")and c.company_id=? and FROM_UNIXTIME(c.create_time/1000,'%Y') <=?");

            onParam.add(cid);
            onParam.add(data.get("stopYear"));
            onParam.add(cid);
            onParam.add(data.get("stopYear"));

            //超期
            overSql.append("select ifnull(count(1),0) as overAmount," +
                    "ifnull(round(sum(if(c.invoice_price is not null,c.invoice_price,c.estimate_price)),2),0) as overPrice" +
                    " from t_scm_car c inner join t_scm_stock s on c.id = s.car_id " +
                    " where TIMESTAMPDIFF(day,DATE_FORMAT(c.enter_warehouse_date_start,'%Y-%m-%d'),DATE_FORMAT(?,'%Y-%m-%d'))>180 " +
                    " and c.company_id=?");
            String stopDay = data.get("stopYear")+"-12-31";
            overParam.add(stopDay);
            overParam.add(cid);
        }

        saleSql.append(" ) t");

        Map maps = new HashMap();
        Map amount = new HashMap();
        Map price = new HashMap();
        Map inMap = this.itScmAmountDao.info(inSql.toString(),inParam.toArray());
        amount.put("in",inMap.get("inAmount"));
        price.put("in",inMap.get("inPrice"));
        Map saleMap = this.itScmAmountDao.info(saleSql.toString(),saleParam.toArray());
        amount.put("sale",saleMap.get("saleAmount"));
        price.put("sale",saleMap.get("salePrice"));
        Map stockMap = this.itScmAmountDao.info(stockSql.toString(),stockParam.toArray());
        amount.put("stock",stockMap.get("stockAmount"));
        price.put("stock",stockMap.get("stockPrice"));
        Map onMap = this.itScmAmountDao.info(onSql.toString(),onParam.toArray());
        amount.put("way",onMap.get("wayAmount"));
        price.put("way",onMap.get("wayPrice"));
        Map overMap = this.itScmAmountDao.info(overSql.toString(),overParam.toArray());
        amount.put("over",overMap.get("overAmount"));
        price.put("over",overMap.get("overPrice"));

        maps.put("amount",amount);
        maps.put("price",price);
        return maps;
    }

    /**
     * 计算同比和环比的时间
     * @param data
     * @param type
     * @return
     * @throws Exception
     */
    private Map calculateTime(Map data,String type) throws Exception{

        Date resultDate = new Date();
        Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
        Map time = new HashMap();

        if(data.get("startDay")!=null&&!data.get("startDay").equals("")){
            ca.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(data.get("startDay").toString()));
            if(type.equals("1")){//同比
                ca.add(Calendar.YEAR, -1); // 年份减1
            }else{ //环比
                ca.add(Calendar.MONTH, -1);// 月份减1
            }
            resultDate = ca.getTime();
            time.put("startDay",new SimpleDateFormat("yyyy-MM-dd").format(resultDate));
        }
        if(data.get("stopDay")!=null&&!data.get("stopDay").equals("")){
            ca.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(data.get("stopDay").toString()));
            if(type.equals("1")){//同比
                ca.add(Calendar.YEAR, -1); // 年份减1
            }else{ //环比
                ca.add(Calendar.MONTH, -1);// 月份减1
            }
            resultDate = ca.getTime();
            time.put("stopDay",new SimpleDateFormat("yyyy-MM-dd").format(resultDate));
        }
        if(data.get("startMonth")!=null&&!data.get("startMonth").equals("")){
            ca.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(data.get("startMonth")+"-01"));
            if(type.equals("1")){//同比
                ca.add(Calendar.YEAR, -1); // 年份减1
            }else{//环比
                ca.add(Calendar.MONTH, -1);// 月份减1
            }
            resultDate = ca.getTime();
            time.put("startMonth",new SimpleDateFormat("yyyy-MM").format(resultDate));
        }
        if(data.get("stopMonth")!=null&&!data.get("stopMonth").equals("")){
            ca.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(data.get("stopMonth")+"-01"));
            if(type.equals("1")){//同比
                ca.add(Calendar.YEAR, -1); // 年份减1
            }else{//环比
                ca.add(Calendar.MONTH, -1);// 月份减1
            }
            resultDate = ca.getTime();
            time.put("stopMonth",new SimpleDateFormat("yyyy-MM").format(resultDate));
        }
        if(data.get("startSeason")!=null&&!data.get("startSeason").equals("")){
            String month = this.findMonth(data.get("startSeason").toString(),"1");
            ca.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(month+"-01"));
            if(type.equals("1")){//同比
                ca.add(Calendar.YEAR, -1); // 年份减1
            }else{//环比
                ca.add(Calendar.MONTH, -1);// 月份减1
            }
            resultDate = ca.getTime();
            time.put("startSeason",new SimpleDateFormat("yyyy-MM").format(resultDate));
        }
        if(data.get("stopSeason")!=null&&!data.get("stopSeason").equals("")){
            String month = this.findMonth(data.get("stopSeason").toString(),"2");
            ca.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(month+"-01"));
            if(type.equals("1")){//同比
                ca.add(Calendar.YEAR, -1); // 年份减1
            }else{//环比
                ca.add(Calendar.MONTH, -1);// 月份减1
            }
            resultDate = ca.getTime();
            time.put("stopSeason",new SimpleDateFormat("yyyy-MM").format(resultDate));
        }

        if(data.get("startYear")!=null&&!data.get("startYear").equals("")){
            ca.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(data.get("startYear")+"-01-01"));
            ca.add(Calendar.YEAR, -1);
            resultDate = ca.getTime();
            time.put("startYear",new SimpleDateFormat("yyyy").format(resultDate));
        }

        if(data.get("stopYear")!=null&&!data.get("stopYear").equals("")){
            ca.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(data.get("stopYear")+"-01-01"));
            ca.add(Calendar.YEAR, -1);
            resultDate = ca.getTime();
            time.put("stopYear",new SimpleDateFormat("yyyy").format(resultDate));
        }

        if(data.get("brand")!=null){
            time.put("brand",data.get("brand"));
        }
        return time;
    }
    /**
     * 季度计算
     * @param season
     * @param type
     * @return
     * @throws Exception
     */
    @Override
    public String findMonth(String season,String type) throws Exception {

        String month = season.substring(4, 5);
        String str = "";
        if (type.equals("1")) { //开始季度
            switch (month) {
                case "1":
                    str = season.substring(0, 4) + "-01";
                    break;
                case "2":
                    str = season.substring(0, 4) + "-04";
                    break;
                case "3":
                    str = season.substring(0, 4) + "-07";
                    break;
                case "4":
                    str = season.substring(0, 4) + "-10";
                    break;
                default: break;
            }
        } else { //结束季度
            switch (month) {
                case "1":
                    str = season.substring(0, 4) + "-03";
                    break;
                case "2":
                    str = season.substring(0, 4) + "-06";
                    break;
                case "3":
                    str = season.substring(0, 4) + "-09";
                    break;
                case "4":
                    str = season.substring(0, 4) + "-12";
                    break;
                default: break;
            }
        }
        return str;
    }
    /**
     * 计算两个日期之间的差
     * @param str1
     * @param str2
     * @return
     * @throws Exception
     */
    @Override
    public long getDistanceDays(String str1, String str2) throws Exception{

        Date one = new SimpleDateFormat("yyyy-MM-dd").parse(str1);//开始;
        Date two = new SimpleDateFormat("yyyy-MM-dd").parse(str2);//结束
        long time1 = one.getTime();
        long time2 = two.getTime();
        return (time2 - time1) / (1000 * 60 * 60 * 24);
    }

    /**
     * 获取最后一天
     * @param month
     * @return
     * @throws Exception
     */
    private String getMonthLastDay(String month) throws Exception{
        month = month +"-01";
        Calendar calendar = Calendar.getInstance();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        // 设置时间,当前时间不用设置
        calendar.setTime(simpleDateFormat.parse(month));
        // 设置日期为本月最大日期
        calendar.set(Calendar.DATE, calendar.getActualMaximum(Calendar.DATE));
        return simpleDateFormat.format(calendar.getTime());
    }

}
